package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import bizlogic.borrowinfo.domain.BorrowInfo;
import utils.JDBCUtil;


public class BorrowInfoDao {
	
    /*
     * 生成自增borrowid
     */
	public String getNextBorrowID() {
		Connection connection = JDBCUtil.getConnection();
		String sql = "select concat('Borrow', extract(year from sysdate()), lpad(max(right(borrowid, 4)) + 1, 4, 0 ) ) as maxnum from borrowinfo where mid(borrowid,7,4) = extract(year from sysdate());";
		PreparedStatement pStatement = null;
		ResultSet resultSet = null;

		String nextid = null;
		try {
			pStatement = connection.prepareStatement(sql);
			resultSet = pStatement.executeQuery();
			while (resultSet.next()) {
				nextid = resultSet.getString("maxnum");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(resultSet, pStatement, connection);
		}
		// 如果一条都不存在，默认生成第一条
		if (nextid == null || "".equals(nextid)) {
			return "Borrow" + new SimpleDateFormat("yyyy").format(new Date()) + "0001";
		} else {
			return nextid;
		}

	}

	
	/*
     * 添加借书信息（返回新增成功的数据条数）
     */
	public int addBorrowInfo(BorrowInfo borrowInfo) {
		Connection con = JDBCUtil.getConnection();
		String sql = "insert into borrowinfo values (?,?,?,?,?,?)";
		String sql2 = "update bookinfo  set remain = remain - 1 where bookid =?";
		PreparedStatement ps = null;
		int i = 0;
		int j = 0;
		int result = 0;
		try {
			con.setAutoCommit(false);
			ps = con.prepareStatement(sql);
			ps.setString(1, borrowInfo.getBorrowid());
			ps.setString(2, borrowInfo.getBookid());
			ps.setString(3, borrowInfo.getBorrower());
			ps.setString(4, borrowInfo.getPhone());
			// 题目中要求精确到时分秒，所以在mysql数据库中，需要设置此字段是datetime
            // 存入时间类型数据：ps.setObject(XX, XXXX); //对比setDate
            // 读取时间类型的数据：rs.getTimestamp("XXX") //对比getDate
			ps.setObject(5, borrowInfo.getBorrowtime());
			ps.setObject(6, borrowInfo.getReturntime());
			i = ps.executeUpdate();
			if (i > 0) {
				ps.close();
				ps = con.prepareStatement(sql2);
				ps.setString(1, borrowInfo.getBookid());
				j = ps.executeUpdate();
			}
			if (i > 0 && j > 0) {
				con.commit();
				result = 1;
			} else {
				con.rollback();
			}
		} catch (Exception e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			JDBCUtil.closeConnection(null, ps, con);
		}
		return result;
	}

	/*
     * 更新借书信息（返回更新成功的数据条数）
     */
	public int updateBorrowInfo(BorrowInfo borrowInfo) {
		Connection connection = JDBCUtil.getConnection();
		String sql = "update borrowinfo  set returntime =? where borrowid =?";
		String sql2 = "update bookinfo  set remain = remain + 1 where bookid =?";
		PreparedStatement pStatement = null;
		int i = 0;
		int j = 0;
		int result = 0;
		try {
			connection.setAutoCommit(false);
			pStatement = connection.prepareStatement(sql);
			pStatement.setObject(1, borrowInfo.getReturntime());
			pStatement.setString(2, borrowInfo.getBorrowid());
			i = pStatement.executeUpdate();
			if (i > 0) {
				pStatement.close();
				pStatement = connection.prepareStatement(sql2);
				pStatement.setString(1, borrowInfo.getBookid());
				j = pStatement.executeUpdate();
			}
			if (i > 0 && j > 0) {
				result = 1;
				connection.commit();
			} else {
				connection.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			JDBCUtil.closeConnection(null, pStatement, connection);
		}
		return result;

	}

	/*
     * 根据借书id删除借书信息（传入单个图书id，返回删除成功的数据条数）
     */
	public int deleteBorrowInfoByBorrowId(String borrowid) {
		Connection connection = JDBCUtil.getConnection();
		String sql = "delete  borrowinfo from borrowinfo where borrowid =?";
		PreparedStatement pStatement = null;
		int i = 0;
		try {
			pStatement = connection.prepareStatement(sql);
			pStatement.setString(1, borrowid);
			i = pStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(null, pStatement, connection);
		}
		return i;
	}

	/*
     * 根据借书id查询借书信息 （返回查询的实体）
     */
	public BorrowInfo queryBorrowInfofoByID(String borrowId) {
		Connection connection = JDBCUtil.getConnection();
		String sql = "select * from  borrowinfo  where borrowid =?";
		PreparedStatement pStatement = null;
		ResultSet resultSet = null;
		try {
			pStatement = connection.prepareStatement(sql);
			pStatement.setString(1, borrowId);
			resultSet = pStatement.executeQuery();
			while (resultSet.next()) {
				BorrowInfo borrowInfo = new BorrowInfo();
				borrowInfo.setBorrowid(borrowId);
				borrowInfo.setBookid(resultSet.getString("bookid"));
				borrowInfo.setBorrower(resultSet.getString("borrower"));
				borrowInfo.setPhone(resultSet.getString("phone"));
				borrowInfo.setBorrowtime(resultSet.getTimestamp("borrowtime"));
				borrowInfo.setReturntime(resultSet.getTimestamp("returntime"));
				return borrowInfo;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(resultSet, pStatement, connection);
		}
		return null;

	}

	/*
     * 根据关键词对借书信息列表进行分页查询（假设关键词为String bookname、Integer booktype）
     */
	public List<BorrowInfo> queryBorrowInfo(int pageIndex, int pageSize, String sortField, String sortOrder) {
		int indexNum = pageIndex * pageSize;
		Connection connection = JDBCUtil.getConnection();
		String sql = " select * from  borrowinfo where 1=1 ";
		StringBuffer sBuffer = new StringBuffer();
		// 拼接sql
		sBuffer.append(sql);
		if (!("".equals(sortField)) && sortField != null) {
			sBuffer.append(" order by ").append(sortField);
			if (!("".equals(sortOrder)) && sortOrder != null) {
				sBuffer.append(" ").append(sortOrder);
			}
		}
		sBuffer.append(" limit ? , ? ");
		sql = sBuffer.toString();
		PreparedStatement pStatement = null;
		ResultSet resultSet = null;
		List<BorrowInfo> lists = new ArrayList<>();
		try {
			pStatement = connection.prepareStatement(sql);
			pStatement.setInt(1, indexNum);
			pStatement.setInt(2, pageSize);
			resultSet = pStatement.executeQuery();
			while (resultSet.next()) {
				BorrowInfo borrowInfo = new BorrowInfo();
				borrowInfo.setBorrowid(resultSet.getString("borrowid"));
				borrowInfo.setBookid(resultSet.getString("bookid"));
				borrowInfo.setBorrower(resultSet.getString("borrower"));
				borrowInfo.setPhone(resultSet.getString("phone"));
				borrowInfo.setBorrowtime(resultSet.getTimestamp("borrowtime"));
				borrowInfo.setReturntime(resultSet.getTimestamp("returntime"));
				lists.add(borrowInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(resultSet, pStatement, connection);
		}
		return lists;
	}

	
	/*
     * 查询借书信息表中所有数据的条数
     */
	public int findLength() {
		Connection connection = JDBCUtil.getConnection();
		String sql = "select *from borrowinfo";
		PreparedStatement pStatement = null;
		ResultSet resultSet = null;
		List<BorrowInfo> lBorrowInfos = new ArrayList<>();
		try {
			pStatement = connection.prepareStatement(sql);
			resultSet = pStatement.executeQuery();
			while (resultSet.next()) {
				BorrowInfo borrowInfo = new BorrowInfo();
				borrowInfo.setBorrowid(resultSet.getString("borrowid"));
				borrowInfo.setBookid(resultSet.getString("bookid"));
				borrowInfo.setBorrower(resultSet.getString("borrower"));
				borrowInfo.setPhone(resultSet.getString("phone"));
				borrowInfo.setBorrowtime(resultSet.getTimestamp("borrowtime"));
				borrowInfo.setReturntime(resultSet.getTimestamp("returntime"));
				lBorrowInfos.add(borrowInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(resultSet, pStatement, connection);
		}
		return lBorrowInfos.size();

	}

	/*
     * 查询是否归还
     */
	public boolean Noreturn(String bookid) {
		Connection connection = JDBCUtil.getConnection();
		PreparedStatement pStatement = null;
		String sql = "select  count(*) as totalcount from borrowinfo where bookid =? and returntime is null ;";
		ResultSet resultSet = null;
		int totalcount = 0;
		try {
			pStatement = connection.prepareStatement(sql);
			pStatement.setString(1, bookid);
			resultSet = pStatement.executeQuery();
			while (resultSet.next()) {
				totalcount = resultSet.getInt("totalcount");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(resultSet, pStatement, connection);
		}
		if (totalcount > 0) {
			return true;
		} else {
			return false;
		}

	}

}
